﻿CREATE PROCEDURE [dbo].[submit_store]
	@in_code nvarchar(20),
	@in_customer nvarchar(20),
	@in_oman nvarchar(20),
	@in_otime smalldatetime,
	@in_date smalldatetime
AS
	declare @weight_in decimal(5,1);
	declare @weight_out decimal(5,1);
	declare @weight_rest decimal(5,1);
	
	declare @rec_in decimal(5,1);
	declare @rec_out decimal(5,1);
	declare @rec_date smalldatetime;
	declare @store money;
	declare @others money;
	declare @total money;
	declare @rest money;
	
	set @store = 0;

	select @weight_in = isnull(sum(inout_in),0) from tb_inout
	where inout_code = @in_code and inout_customer = @in_customer;
	
	select @weight_out = isnull(sum(inout_out),0) from tb_inout
	where inout_code = @in_code and inout_customer = @in_customer;
	
	declare inout_cursor cursor for 
	select inout_in, inout_out, inout_date from tb_inout
	where inout_code = @in_code and inout_customer = @in_customer;
	open inout_cursor;
	fetch next from inout_cursor into @rec_in, @rec_out, @rec_date;
	while @@fetch_status = 0 begin
	
		if (@rec_out = 0)
			set @store = @store + (datediff(d,@rec_date,@in_date) + 1) * 0.4 * @rec_in;
		if (@rec_in = 0)
			set @store = @store - datediff(d,@rec_date,@in_date) * 0.4 * @rec_out;

		fetch next from inout_cursor into @rec_in, @rec_out, @rec_date;
	end
	close inout_cursor;
	deallocate inout_cursor;
	
	select @others = isnull(sum(others_price),0) from tb_others
	where others_code = @in_code and others_customer = @in_customer;
	
	declare @store2 money;
	declare @others2 money;
	
	select @store2 = isnull(sum(deal_store),0) from tb_deal
	where deal_code = @in_code and deal_customer = @in_customer;
	
	select @others2 = isnull(sum(deal_others),0) from tb_deal
	where deal_code = @in_code and deal_customer = @in_customer;
	
	set @total = @store + @others;
	set @store = @store - @store2;
	set @others = @others - @others2;
	set @rest = @store + @others;

	if exists(select * from tb_deal where deal_code = @in_code and deal_customer = @in_customer)
	
		update tb_deal set deal_store = @store, deal_others = @others, deal_total = @total,
		deal_rest = @rest, deal_date = @in_date, deal_credit = '未开', deal_oman = @in_oman,
		deal_otime = @in_otime where deal_code = @in_code and deal_customer = @in_customer;

	else

		insert into tb_deal(deal_code, deal_customer, deal_store, deal_others,
		deal_discount, deal_total, deal_rest, deal_date, deal_credit, deal_oman,
		deal_otime)
		values (@in_code, @in_customer, @store, @others, 1.0, @total,
		@total, @in_date, '未开', @in_oman, @in_otime);
